# -*- coding: UTF-8 -*-
'''
Created on 18/10/2014

@author: Eduardo Cardeira - Student Number: 6033

This module contains the class and methods that allow reading the file
IPC_Portugal_1977_2013.xls and storing it's data in Database.db for later use.
'''

import sqlite3

from xlrd import open_workbook


class ReadAndStore:
    """
    The ReadAndStore class.
    Contains methods to read data from an xls file and store it in a database.
    """

    def __init__(self, database_name, table_name, workbook_name):
        """
        The instantiation method for ReadAndStore.

        Arguments:

        database_name - the name of the database to be created or connected to
        table_name - the name of the database table the data will be stored in
        workbok_name - the name of the workbook with the data to be read
        """
        self.database_name = database_name
        self.workbook_name = workbook_name
        self.table_name = table_name
        pass
    pass

    def open_workbook(self):
        """
        The open_workbook method.
        Tries to open and return a pointer to the given workbook.
        Raises an exception and present a message in case the operation fails.

        Returns_:

        xls_file - the pointer to the workbook
        """
        try:
            xls_file = open_workbook(self.workbook_name)
            print self.workbook_name + " workbook opened successfully!"
            return xls_file
        except IOError:
            print "Could not read the " + self.workbook_name + " workbook file\
 , the file may not exist or be missing."
        pass
    pass

    def connect_database(self):
        """
        The connect_database method.
        Tries to create or connect to a given database and returns a pointer to
        said database.
        Raises an exception and presents a message in case the operation fails.

        Returns:

        connection - the pointer to the database
        """
        try:
            connection = sqlite3.connect(self.database_name)
            print self.database_name + " database creation/connection \
established successfully!"
            return connection
        except IOError:
            print "Could not create the " + self.database_name + " database or\
 the database file is missing and could not be connected to."
            pass
        pass
    pass

    def drop_table(self):
        """
        The drop_table method.
        Drops the data table if the table already existed in the database.
        """
        connection = self.connect_database()
        cursor = connection.cursor()
        cursor.execute(('''DROP TABLE IF EXISTS "{0}"''').
                       format(self.table_name))
        connection.commit()
        connection.close()
        print "The " + self.table_name + " table was dropped successfully!"
        pass
    pass

    def get_fields(self):
        """
        The get_fields method.
        Obtains the names of each column in the workbook sheet and returns
        them to allow the creation of the database table with those fields.

        Returns:

        table_fields - the list of table field names
        """
        xls_file = self.open_workbook()
        data_sheet = xls_file.sheet_by_index(0)    # index 0 = sheet Dados.
        table_fields = []    # stores sheet column headers as database fields.
        table_fields.append("Id de Linha")    # new line number id field.
        # the cycle starts at 1 to jump the empty column in the data sheet.
        for col_cell in range(1, data_sheet.ncols):
            table_fields.append((data_sheet.cell_value(0, col_cell)).
                                encode('UTF-8'))
            pass
        return table_fields
        pass
    pass

    def create_table(self):
        """
        The create_table method.
        Creates a table in the database with the same fields as the data sheet
        of the same name from the previously given workbook.
        """
        connection = self.connect_database()
        cursor = connection.cursor()
        table_fields = self.get_fields()
        cursor.execute(('''CREATE TABLE IF NOT EXISTS "{0}"
                        ("{1}" INTEGER PRIMARY KEY,
                         "{2}" INTEGER,
                         "{3}" FLOAT,
                         "{4}" FLOAT,
                         "{5}" FLOAT,
                         "{6}" FLOAT,
                         "{7}" FLOAT,
                         "{8}" FLOAT,
                         "{9}" FLOAT,
                         "{10}" FLOAT)''').format(self.table_name,
                                                  table_fields[0],
                                                  table_fields[1],
                                                  table_fields[2],
                                                  table_fields[3],
                                                  table_fields[4],
                                                  table_fields[5],
                                                  table_fields[6],
                                                  table_fields[7],
                                                  table_fields[8],
                                                  table_fields[9]))
        connection.commit()
        connection.close()
        print "The " + self.table_name + " table was created successfully!"
        pass
    pass

    def insert_data(self):
        """
        The insert_data method.
        Reads the data from the given xls file sheet and stores it into the
        table of the same name of the given database.
        """
        connection = self.connect_database()
        cursor = connection.cursor()
        xls_file = self.open_workbook()
        data_sheet = xls_file.sheet_by_index(0)    # index 0 = sheet Dados.
        table_fields = self.get_fields()
        # table_values will store the values of the cells for each line of the
        # data sheet while cycling through it.
        table_values = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9]
        line_number = 0  # This number will be stored in the Id de Linha field.
        # the cycle starts at 1 to jump the header names row in the data sheet.
        for row_cell in range(1, data_sheet.nrows):
            line_number += 1
            table_values[0] = line_number
            # the cycle starts at 1 to jump the empty column in the data sheet.
            for col_cell in range(1, data_sheet.ncols):
                table_values[col_cell] = data_sheet.cell_value(
                    row_cell, col_cell)
                pass
            cursor.execute(('''INSERT INTO "{0}"
                            ("{1}", "{2}", "{3}", "{4}", "{5}",
                             "{6}", "{7}", "{8}", "{9}", "{10}") VALUES
                            ("{11}", "{12}", "{13}", "{14}", "{15}",
                             "{16}", "{17}","{18}", "{19}", "{20}")''').
                           format(self.table_name, table_fields[0],
                                  table_fields[1], table_fields[2],
                                  table_fields[3], table_fields[4],
                                  table_fields[5], table_fields[6],
                                  table_fields[7], table_fields[8],
                                  table_fields[9], table_values[0],
                                  table_values[1], table_values[2],
                                  table_values[3], table_values[4],
                                  table_values[5], table_values[6],
                                  table_values[7], table_values[8],
                                  table_values[9]))
            pass
        pass
        connection.commit()
        connection.close()
        print "Finished inserting data into the database successfully!"
    pass

# Code execution to create and fill the database.
database = ReadAndStore("Database.db", "Dados", "IPC_Portugal_1977_2013.xls")
database.drop_table()
database.create_table()
database.insert_data()
